import xlrd, json, os

data = xlrd.open_workbook('人员2.xlsx')
table = data.sheets()[0]
n = table.nrows
p = []
for i in range(n):
    if i == 0:
        continue
    # table.row_values(i)
    p.append(table.row_values(i))
q = []
# 判断数据库文件是否存在
if os.path.isfile("p.json"):
    # 读取数据库p.json中的数据，保存到变量q里面
    with open("p.json", "r", encoding="utf-8")as j:
        q = json.load(j)

p = p + q
# 对二维列表去重
reList = list(set([tuple(t) for t in p]))
#转成json格式,防止序列化时中文乱码，需要添加参数ensure_ascii=False
json_data = json.dumps(reList, ensure_ascii=False)

#写入到p文件中，r表示只读模式，w表示覆盖写，a+表示追加模式，没有文件时会去创建
with open('p.json',"w",encoding="utf-8")as f:
    f.write(json_data)
print("所有人员：")
for i in range(len(reList)):
    print(reList[i])
print("年龄大于等于40岁的人员：")
s = 0
for i in range(len(reList)):
    s = s + reList[i][6]
    if reList[i][6] >= 40:
        print(reList[i])

print("平均年龄是：%f" % (s / len(reList)))
